﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace MDIApplication.Classes
{
    class BookingRoomAvailabilityDBManager
    {
    
        #region insertBooking
        public bool insertBookingtoDB(Booking nb)
        {
            SqlConnection conn = null;
            bool result = false;
            try
            {             
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                // set the parameters of the sql insert statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Booking(Booking_ID,Check_in_Date,Check_out_Date,Nights_of_Stay,No_of_Adults,No_of_Children,Additional_Remarks,Late_Checkout_Indication,Room_Unit_No,Guest_ID)" + " VALUES (@bookingId,@checkInDate,@checkOutDate,@numberOfAdults,@numberOfChildren,@nightsOfStay,@additionalRemarks,@lateCheckOut,@roomUnitNo,@guestId)";

                comm.Parameters.AddWithValue("@bookingId", nb.bookingId);
                comm.Parameters.AddWithValue("@checkInDate", nb.checkInDate);
                comm.Parameters.AddWithValue("@checkOutDate", nb.checkOutDate);
                comm.Parameters.AddWithValue("@numberOfAdults", nb.numberOfAdults);
                comm.Parameters.AddWithValue("@numberOfChildren", nb.numberOfChildren);
                comm.Parameters.AddWithValue("@nightsOfStay", nb.nightsOfStay);
                comm.Parameters.AddWithValue("@additionalRemarks", nb.additionalRemarks);
                comm.Parameters.AddWithValue("@lateCheckOut", nb.lateCheckOut);
                comm.Parameters.AddWithValue("@roomUnitNo", nb.roomUnitNo);
                comm.Parameters.AddWithValue("@guestId", nb.guestId);
                
                // execute the sql insert statement
                if (comm.ExecuteNonQuery() > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                MessageBox.Show("Create Booking Record failed. This is because " + e.Message);
            }
            
            return result;
        }
        #endregion insertBooking

        #region insertGuest
        public bool insertGuesttoDB(Guest ng)
        {
            SqlConnection conn = null;
            bool result = false;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                // set the parameters of the sql insert statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Guest(Guest_ID,First_Name,Last_Name,IC_No,Date_of_Birth,Gender,Address,Contact_No,Email)" + " VALUES (@guestId,@firstName,@lastName,@icNo,@dateOfBirth,@gender,@address,@contactNo,@email)";

                comm.Parameters.AddWithValue("@guestId", ng.guestId);
                comm.Parameters.AddWithValue("@firstName", ng.firstName);
                comm.Parameters.AddWithValue("@lastName", ng.lastName);
                comm.Parameters.AddWithValue("@icNo", ng.icNo);
                comm.Parameters.AddWithValue("@dateOfBirth", ng.dateOfBirth);
                comm.Parameters.AddWithValue("@gender", ng.gender);
                comm.Parameters.AddWithValue("@address", ng.address);
                comm.Parameters.AddWithValue("@contactNo", ng.contactNo);
                comm.Parameters.AddWithValue("@email", ng.email);
                
                // execute the sql insert statement
                if (comm.ExecuteNonQuery() > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                MessageBox.Show("Create Guest Record failed. This is because " + e.Message);
            }

            return result;
        }
        #endregion insertGuest

        #region insertInvoice
        public bool insertInvoice(Invoice i)
        {
            SqlConnection conn = null;
            bool result = false;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                // set the parameters of the sql insert statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Invoice(Invoice_ID,Invoice_Date,Guest_ID,Booking_ID,Room_Expense,Credit_Card_No,Additional_Cost,Total_Payment)" + " VALUES (@invoiceId,@invoiceDate,@guestId,@bookingId,@roomExpense,@creditCardNo,@additionalCost,@totalPayment)";

                comm.Parameters.AddWithValue("@invoiceId", i.invoiceId);
                comm.Parameters.AddWithValue("@invoiceDate", i.invoiceDate);
                comm.Parameters.AddWithValue("@guestId", i.guestId);
                comm.Parameters.AddWithValue("@bookingId", i.bookingId);
                comm.Parameters.AddWithValue("@roomExpense", i.roomExpense);
                comm.Parameters.AddWithValue("@creditCardNo", i.creditCardNo);
                comm.Parameters.AddWithValue("@additionalCost", i.additionalCost);
                comm.Parameters.AddWithValue("@totalPayment", i.totalPayment);

                // execute the sql insert statement
                if (comm.ExecuteNonQuery() > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                MessageBox.Show("Create Invoice Record failed. This is because " + e.Message);
            }

            return result;
        }
        #endregion insertInvoice

        #region insertCreditCard
        public bool insertCreditCard(CreditCard cc)
        {
            SqlConnection conn = null;
            bool result = false;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                // set the parameters of the sql insert statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CreditCard(Credit_Card_No,Card_Type,Card_Holder_Name,Expiry_Date)" + " VALUES (@creditCardNo,@cardType,@cardHolderName,@expiryDate)";

                comm.Parameters.AddWithValue("@creditCardNo", cc.creditCardNo);
                comm.Parameters.AddWithValue("@cardType", cc.cardType);
                comm.Parameters.AddWithValue("@cardHolderName", cc.cardHolderName);
                comm.Parameters.AddWithValue("@expiryDate", cc.expiryDate);


                // execute the sql insert statement
                if (comm.ExecuteNonQuery() > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                MessageBox.Show("Create Credit Card Record failed. This is because " + e.Message);
            }

            return result;
        }
        #endregion insertCreditCard

        #region getBookingByBookingID
        public Booking getBookingByBookingID(string bookingId)
        {
            Booking bk = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Booking WHERE Booking_ID=@bookingId";
                comm.Parameters.AddWithValue("@bookingId", bookingId);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {                   
                    bk = new Booking();
                    bk.bookingId = (string)dr["Booking_ID"];
                    bk.checkInDate = Convert.ToDateTime(dr["Check_in_Date"]);
                    bk.checkOutDate=Convert.ToDateTime(dr["Check_out_Date"]);
                    bk.nightsOfStay=Convert.ToInt32(dr["Nights_of_Stay"]);
                    bk.numberOfAdults=Convert.ToInt32(dr["No_of_Adults"]);
                    bk.numberOfChildren=Convert.ToInt32(dr["No_of_Children"]);
                    bk.additionalRemarks=(string)dr["Additional_Remarks"];
                    bk.lateCheckOut =(string)dr["Late_Checkout_Indication"];
                    bk.roomUnitNo = Convert.ToInt32(dr["Room_Unit_No"]);
                    bk.guestId = (string)dr["Guest_ID"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                MessageBox.Show("Retrieve Booking failed. This is because " + e.Message);
            }

            return bk;

        }
        #endregion getBookingByBookingID

        #region getGuestByGuestID
        public Guest getGuestByGuestID(string guestId)
        {
            Guest g = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Guest WHERE Guest_ID=@guestId";
                comm.Parameters.AddWithValue("@guestId", guestId);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    g = new Guest();
                    g.guestId = (string)dr["Guest_ID"];              
                    g.firstName=(string)dr["First_Name"];
                    g.lastName=(string)dr["Last_Name"];
                    g.icNo = (string)dr["IC_No"];
                    g.dateOfBirth=Convert.ToDateTime(dr["Date_of_Birth"]);
                    g.gender=(string)dr["Gender"];
                    g.address=(string)dr["Address"];
                    g.contactNo = Convert.ToInt32(dr["Contact_No"]);
                    g.email=(string)dr["Email"];                 
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                MessageBox.Show("Retrieve Guest failed. This is because " + e.Message);
            }

            return g;

        }
        #endregion getGuestByGuestID

        #region getGuestByGuestName
        public Guest getGuestByGuestName(string firstName,string lastName)
        {
            Guest g = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Guest WHERE First_Name=@firstName and Last_Name=@lastName ";
                comm.Parameters.AddWithValue("@firstName", firstName);
                comm.Parameters.AddWithValue("@lastName", lastName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    g = new Guest();
                    g.guestId = (string)dr["Guest_ID"];
                    g.firstName = (string)dr["First_Name"];
                    g.lastName = (string)dr["Last_Name"];
                    g.icNo = (string)(dr["IC_No"]);
                    g.dateOfBirth = Convert.ToDateTime(dr["Date_of_Birth"]);
                    g.gender = (string)dr["Gender"];
                    g.address = (string)dr["Address"];
                    g.contactNo = Convert.ToInt32(dr["Contact_No"]);
                    g.email = (string)dr["Email"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                MessageBox.Show("Retrieve Guest failed. This is because " + e.Message);
            }

            return g;

        }
        #endregion getGuestByGuestName

        #region getAllBookings
        public List<Booking> getAllBookings()
        {
            List<Booking> bookingList = new List<Booking>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Booking";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Booking bk = new Booking();
                    bk.bookingId=(string)dr["Booking_ID"];
                    bk.checkInDate=Convert.ToDateTime(dr["Check_in_Date"]);
                    bk.checkOutDate=Convert.ToDateTime(dr["Check_out_Date"]);
                    bk.nightsOfStay=Convert.ToInt32(dr["Nights_of_Stay"]);
                    bk.numberOfAdults=Convert.ToInt32(dr["No_of_Adults"]);
                    bk.numberOfChildren=Convert.ToInt32(dr["No_of_Children"]);
                    bk.additionalRemarks=(string)dr["Additional_Remarks"];
                    bk.lateCheckOut=(string)dr["Late_Checkout_Indication"];
                    bk.roomUnitNo=Convert.ToInt32(dr["Room_Unit_No"]);
                    bk.guestId = (string)dr["Guest_ID"];
                    bookingList.Add(bk);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Create Booking failed. This is because " + ex.Message);
            }
            return bookingList;
        }
        #endregion getAllBookings

        #region getAllGuests
        public List<Guest> getAllGuests()
        {
            List<Guest> guestList = new List<Guest>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Guest";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Guest g = new Guest();
                    g.guestId=(string)dr["Guest_ID"];
                    g.firstName=(string)dr["First_Name"];
                    g.lastName=(string)dr["Last_Name"];
                    g.icNo=(string)dr["IC_No"];
                    g.dateOfBirth=Convert.ToDateTime(dr["Date_of_Birth"]);
                    g.gender=(string)dr["Gender"];
                    g.address=(string)dr["Address"];
                    g.contactNo=Convert.ToInt32(dr["Contact_No"]);
                    g.email=(string)dr["Email"];
                    guestList.Add(g);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Booking Creation is failed. This is because " + ex.Message);
            }
            return guestList;
        }
        #endregion getAllGuests

        #region getAllRooms
        public List<Room> getAllRooms()
        {
            List<Room> roomList = new List<Room>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Room";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room r = new Room();
                    r.roomUnitNo=Convert.ToInt32(dr["Room_Unit_No"]);
                    r.roomLevel=Convert.ToInt32(dr["Room_Level"]);;
                    r.roomType=(string)dr["Room_Type"];;
                    r.roomStatus=(string)dr["Room_Status"];;
                    r.roomRate = Convert.ToDouble(dr["Room_Rate"]);
                    roomList.Add(r);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Retrieve Room failed. This is because " + ex.Message);
            }
            return roomList;
        }
        #endregion getAllRooms

        #region getAllInvoice
        public List<Invoice> getAllInvoice()
        {
            List<Invoice> invoiceList = new List<Invoice>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Invoice";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Invoice i = new Invoice();
                    i.invoiceId = (string)dr["Invoice_ID"];
                    i.invoiceDate = Convert.ToDateTime(dr["Invoice_Date"]);
                    i.guestId = (string)dr["Guest_ID"];
                    i.bookingId = (string)dr["Booking_ID"];                   
                    i.roomExpense=Convert.ToDouble(dr["Room_Expense"]);
                    i.creditCardNo=(string)dr["Credit_Card_No"];
                    i.additionalCost = Convert.ToDouble(dr["Additional_Cost"]);
                    i.totalPayment=Convert.ToDouble(dr["Total_Payment"]);
                    invoiceList.Add(i);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Retrieve Invoice failed. This is because " + ex.Message);
            }
            return invoiceList;
        }
        #endregion getAllInvoice

        #region getRoomByRoomUnitNo
        public Room getRoomByRoomUnitNo(int roomUnitNo)
        {
            Room r = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room WHERE Room_Unit_No=@roomUnitNo";
                comm.Parameters.AddWithValue("@roomUnitNo", roomUnitNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    r = new Room();
                    r.roomUnitNo = Convert.ToInt32(dr["Room_Unit_No"]);
                    r.roomLevel = Convert.ToInt32(dr["Room_Level"]);
                    r.roomType = (string)dr["Room_Type"];
                    r.roomStatus = (string)dr["Room_Status"];
                    r.roomRate = Convert.ToDouble(dr["Room_Rate"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                MessageBox.Show("Retrieve Room failed. This is because " + e.Message);
            }
            return r;
        }
        #endregion getRoomByRoomUnitNo

        #region updateRoom
        public void updateRoom(Room r)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Room SET Room_Unit_No=@roomUnitNo,Room_Level=@roomLevel,Room_Type=@roomType,Room_Status=@roomStatus where Room_Unit_No=@roomUnitNo";
                comm.Parameters.AddWithValue("@roomUnitNo", r.roomUnitNo);
                comm.Parameters.AddWithValue("@roomLevel", r.roomUnitNo);
                comm.Parameters.AddWithValue("@roomType", r.roomUnitNo);
                comm.Parameters.AddWithValue("@roomStatus", r.roomStatus);
                comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Update Room failed. This is because " + ex.Message);

            }
        }
        #endregion updateRoom

        #region updateRoomStatus
        public  void updateRoomStatus(int roomUnitNo, String status)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Room SET Room_Status=@status where Room_Unit_No=@roomUnitNo";
                comm.Parameters.AddWithValue("@roomUnitNo", roomUnitNo);
                comm.Parameters.AddWithValue("@status",status);
                comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Update Room failed. This is because " + ex.Message);

            }

        }
        #endregion updateRoomStatus

        #region updateGuestInfo
        public void updateGuestInfo(Guest ng)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Guest SET Guest_ID=@guestId,First_Name = @firstName,Last_Name=@lastName,IC_No=@icNo,Date_of_Birth=@dateOfBirth,Gender=@gender,Address=@address,Contact_No=@contactNo,Email=@email where Guest_ID=@guestId";
                comm.Parameters.AddWithValue("@guestId", ng.guestId);
                comm.Parameters.AddWithValue("@firstName", ng.firstName);
                comm.Parameters.AddWithValue("@lastName", ng.lastName);
                comm.Parameters.AddWithValue("@icNo", ng.icNo);
                comm.Parameters.AddWithValue("@dateOfBirth", ng.dateOfBirth);
                comm.Parameters.AddWithValue("@gender", ng.gender);
                comm.Parameters.AddWithValue("@address", ng.address);
                comm.Parameters.AddWithValue("@contactNo", ng.contactNo);
                comm.Parameters.AddWithValue("@email", ng.email);
                comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Update Room failed. This is because " + ex.Message);

            }

        }
        #endregion updateGuestInfo

        #region DeleteGuestByGuestId
        public void DeleteGuestByGuestId(string guestId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm1 = new SqlCommand();
                comm1.Connection = conn;
                comm1.CommandText = "Delete From Invoice where Guest_ID=@guestId"; 
                comm1.Parameters.AddWithValue("@guestId", guestId);
                comm1.ExecuteNonQuery();

                SqlCommand comm2 = new SqlCommand();
                comm2.Connection = conn;
                comm2.CommandText = "Delete From Booking where Guest_ID=@guestId";
                comm2.Parameters.AddWithValue("@guestId", guestId);
                comm2.ExecuteNonQuery();

                SqlCommand comm3 = new SqlCommand();
                comm3.Connection = conn;
                comm3.CommandText = "Delete From Guest where Guest_ID=@guestId";
                comm3.Parameters.AddWithValue("@guestId", guestId);
                comm3.ExecuteNonQuery();

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Delete Guest failed. This is because " + ex.Message);
            }
        }
        #endregion DeleteGuestByGuestId

    }
}
